﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class UC_BCDTNVKD : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBaoCaoDoanhThu";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0, ttban = 0, tongchenh = 0, chenhnvkd = 0, ttchenh = 0, ttchenhnvkd = 0, htrocuoc = 0, tiencabom = 0;
    static DataTable dt, dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;

            LoadNVKD(dlNhanVien);
            LoadHinhThuc(dlHinhThuc);
        }
    }
    void LoadNVKD(DropDownList dlNV)
    {
        dlNV.Items.Clear();
        dlNV.Items.Insert(0, new ListItem("--Chọn nhân viên kinh doanh--", ""));
        dlNV.SelectedIndex = 0;
    }
    void LoadHinhThuc(DropDownList dlHinhThuc)
    {
        dlHinhThuc.Items.Clear();
        dlHinhThuc.Items.Insert(0, new ListItem("--Chọn hình thức HĐ--", "0"));
        dlHinhThuc.SelectedIndex = 0;
    }
    void LoadNVKDByTime(DropDownList dlNV)
    {
        dlNV.Items.Clear();
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlNV.DataSource = ex.GetData("sp_BCDT_NVKD_LoadNVByTime", p);
        dlNV.DataBind();
        dlNV.Items.Insert(0, new ListItem("--Chọn nhân viên--", ""));
        dlNV.SelectedIndex = 0;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCDTTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCDTMacHopDong.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCDTBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(3))
            Response.Redirect("BCDTNVKD.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
            Response.Redirect("BCDTXeBom.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
            Response.Redirect("BCDTGiaVatLieu.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
            Response.Redirect("BCDTKhachHang.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(7))
            Response.Redirect("BCDTVatLieuTieuHao.aspx");
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }

        getDate();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        LoadNVKDByTime(dlNhanVien);

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        Search(1);
        btnFirst.Enabled = false;
        btnNext.Enabled = true;
        btnPre.Enabled = false;


    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        int index = 1;
        if (dlNhanVien.SelectedIndex.Equals(0) && dlHinhThuc.SelectedIndex.Equals(0))
            index = 1;
        else if (dlNhanVien.SelectedIndex > 0 && dlHinhThuc.SelectedIndex.Equals(0))
            index = 2;
        else if (dlNhanVien.SelectedIndex > 0 && dlHinhThuc.SelectedIndex > 0)
            index = 3;

        getDate();
        p = new SqlParameter[5];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;

        try
        {
            dt = ex.GetData("sp_BCDT_NVKD_Print", p);
            PrintNPOI(dt);
        }
        catch (Exception ax)
        {
            gstGetMess(ax.Message, "");
        }
    }
    void Search(int page)
    {
        int index = 1;
        if (dlNhanVien.SelectedIndex.Equals(0) && dlHinhThuc.SelectedIndex.Equals(0))
            index = 1;
        else if (dlNhanVien.SelectedIndex > 0 && dlHinhThuc.SelectedIndex.Equals(0))
            index = 2;
        else if (dlNhanVien.SelectedIndex > 0 && dlHinhThuc.SelectedIndex > 0)
            index = 3;

        getDate();
        p = new SqlParameter[7];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@PageNumber", page);
        p[5].SqlDbType = SqlDbType.Int;
        p[6] = new SqlParameter("@RowspPage", PageNumber);
        p[6].SqlDbType = SqlDbType.Int;

        dt = ex.GetData("sp_BCDT_NVKD", p);

        GV.DataSource = dt;
        GV.DataBind();

        if (CurrentPage.Equals(1))
        {
            p = new SqlParameter[5];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
            p[3].SqlDbType = SqlDbType.Int;
            p[4] = new SqlParameter("@Index", index);
            p[4].SqlDbType = SqlDbType.Int;

            dt = ex.GetData("sp_BCDT_NVKD_GetFooter", p);
            if (dt.Rows.Count > 0)
            {
                kltx = 0; klban = 0; ttban = 0; tongchenh = 0; chenhnvkd = 0; ttchenh = 0; ttchenhnvkd = 0; htrocuoc = 0; tiencabom = 0;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    kltx += double.Parse(dt.Rows[0]["KLThucXuat"].ToString());
                    klban += double.Parse(dt.Rows[0]["KLKhachHang"].ToString());
                    ttban += double.Parse(dt.Rows[0]["TTGiaHD"].ToString());
                    tongchenh += double.Parse(dt.Rows[0]["ChenhKL"].ToString());
                    chenhnvkd += double.Parse(dt.Rows[0]["KLNVKD"].ToString());
                    ttchenh += double.Parse(dt.Rows[0]["TTChenh"].ToString());
                    ttchenhnvkd += double.Parse(dt.Rows[0]["TTNVKD"].ToString());
                    htrocuoc += double.Parse(dt.Rows[0]["TienHoTroCuoc"].ToString());
                    //tiencabom += double.Parse(dt.Rows[0]["TienCaBom"].ToString());
                }

                p = new SqlParameter[5];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
                p[3].SqlDbType = SqlDbType.Int;
                p[4] = new SqlParameter("@Index", index);
                p[4].SqlDbType = SqlDbType.Int;

                dt = ex.GetData("sp_BCDT_NVKD_GetTongTienBom", p);
                if (dt.Rows.Count > 0)
                {
                    tiencabom = double.Parse(dt.Rows[0]["TienCaBom"].ToString().Trim().Equals("") ? "0" : dt.Rows[0]["TienCaBom"].ToString());
                }
            }
        }
        if (GV.Rows.Count > 0)
        {
            GV.FooterRow.Cells[2].Text = "Tổng cộng";
            GV.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
            GV.FooterRow.Cells[5].Text = ex.ConvertDecimal(kltx);
            GV.FooterRow.Cells[7].Text = ex.ConvertDecimal(klban);
            GV.FooterRow.Cells[10].Text = ex.ConvertDecimal(ttban);
            GV.FooterRow.Cells[11].Text = ex.ConvertDecimal(tongchenh);
            GV.FooterRow.Cells[12].Text = ex.ConvertDecimal(chenhnvkd);
            GV.FooterRow.Cells[13].Text = ex.ConvertDecimal(ttchenh);
            GV.FooterRow.Cells[14].Text = ex.ConvertDecimal(ttchenhnvkd);
            GV.FooterRow.Cells[15].Text = ex.ConvertDecimal(htrocuoc);
            GV.FooterRow.Cells[17].Text = ex.ConvertDecimal(tiencabom);
            GV.FooterRow.Cells[18].Text = ex.ConvertDecimal(tiencabom + htrocuoc + ttban);
        }
    }

    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "BCDT-NVKD";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;


            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            //hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper();
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            rowIndex++;
            string ht = "";
            if (dlNhanVien.SelectedIndex > 0)
            {
                if (dlHinhThuc.SelectedIndex > 0)
                    ht = "-Hình thức: " + dlHinhThuc.SelectedItem.Text;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Nhân viên KD: " + dlNhanVien.SelectedItem.Text + "" + ht);
                r1c1.CellStyle = hs3;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(3, 3, 0, 12);
                sheet.AddMergedRegion(cra);
            }
            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion
            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày xuất", "Khách hàng", "VAT", "Mác TX", "KL TX", "Mác HĐ", "KL bán", "Loại đá", "Giá HĐ", "TT bê tông", "Tổng chênh", "Chênh NVKD", "TT chênh", "TT chênh NVKD", "Hỗ trợ cước", "Loại ca bơm", "Tiền ca bơm", "Thành tiền hàng", "NVKD", "Hình thức HĐ" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            int sodong = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);
                    if ((j == 5 || j == 7) || j >= 9 && j != 16 && j != 20 && j != 19)
                    {
                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                        }
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j].ToString());

                        cell.SetCellValue(dt.Rows[i][j].ToString());
                        if (j <= 8 && j != 2)
                            cell.CellStyle = hsCenter;
                        else
                            if (j >= 9 && j != 16 && j != 20 && j != 19)
                                cell.CellStyle = hsRight;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }
                if (i > 0)
                {
                    if (dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()) && dt.Rows[i][2].ToString().Equals(dt.Rows[i - 1][2].ToString()) && dt.Rows[i][16].ToString().Equals(dt.Rows[i - 1][16].ToString()) && dt.Rows[i][16].ToString().ToLower().Contains("ca"))
                    {
                        sodong++;
                        cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 17, 17);
                        sheet.AddMergedRegion(cra);
                    }
                    else
                        sodong = 0;
                }

            }
            #region Footer
            //-----------Footer
            string[] footer = { "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P" };
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            for (int k = 5; k <= 15; k++)
            {
                if (k != 6 && k != 8 && k != 9)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }
            }
            cell = row.CreateCell(17);
            cell.SetCellValue(GV.FooterRow.Cells[17].Text.Trim());
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            cell = row.CreateCell(18);
            cell.SetCellValue(GV.FooterRow.Cells[18].Text.Trim());
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            //fix lai border
            for (int h = 0; h <= 20; h++)
            {
                if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 19 || h == 6 || h == 8 || h == 9)
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }

            }
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            #endregion
            //end sheet tong hop--------------------------

            double cabom = 0;
            sodong = 0;
            string hinhthuc = "";
            if (dlNhanVien.SelectedIndex.Equals(0))
            {

                getDate();
                p = new SqlParameter[5];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
                p[3].SqlDbType = SqlDbType.Int;
                p[4] = new SqlParameter("@Index", 1);
                p[4].SqlDbType = SqlDbType.Int;

                dt = ex.GetData("sp_BCDT_NVKD_Print_NhanVien", p);

                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        hinhthuc = dt.Rows[i]["NVKD"].ToString();

                        if (!hinhthuc.Equals(dt.Rows[i - 1]["NVKD"].ToString()))
                        {
                            cabom = 0;
                            STT = 0;
                            #region Footer
                            //-----------Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(2);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 5; k <= 15; k++)
                            {
                                if (k != 6 && k != 8 && k != 9)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }
                            //fix lai border
                            for (int h = 0; h <= 20; h++)
                            {
                                if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 18 || h == 19 || h == 17 || h == 6 || h == 8 || h == 9)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }

                            }
                            for (int ik = 0; ik < dt.Columns.Count; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                            //---End Footer
                            #endregion
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(hinhthuc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            rowIndex++;

                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Nhân viên KD: " + hinhthuc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                            //End header-----------------------------------------------------------------------

                            STT++;

                        }
                    }
                    else
                    {
                        hinhthuc = "";
                        if (!hinhthuc.Equals(dt.Rows[i]["NVKD"].ToString()))
                        {
                            hinhthuc = dt.Rows[i]["NVKD"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(hinhthuc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Nhân viên KD: " + hinhthuc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= 20; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;


                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        if ((j == 5 || j == 7) || j >= 9 && j != 16 && j != 20 && j != 19)
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());

                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            if (j <= 8 && j != 2)
                                cell.CellStyle = hsCenter;
                            else
                                if (j >= 9 && j != 16 && j != 20 && j != 19)
                                    cell.CellStyle = hsRight;
                                else
                                    cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }
                    if (i > 0)
                    {
                        if (dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()) && dt.Rows[i][2].ToString().Equals(dt.Rows[i - 1][2].ToString()) && dt.Rows[i][16].ToString().Equals(dt.Rows[i - 1][16].ToString()) && dt.Rows[i][16].ToString().ToLower().Contains("ca"))
                        {
                            sodong++;
                            cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 17, 17);
                            sheet.AddMergedRegion(cra);
                        }
                        else
                            sodong = 0;
                    }

                    #region Footer
                    if (i == dt.Rows.Count - 1)
                    {
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(2);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int k = 5; k <= 15; k++)
                        {
                            if (k != 6 && k != 8 && k != 9)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                        }
                        //fix lai border
                        for (int h = 0; h <= 20; h++)
                        {
                            if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 19 || h == 18 || h == 17 || h == 6 || h == 8 || h == 9)
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }

                        }
                        for (int ik = 0; ik < dt.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                }

            }
            else
                if (dlHinhThuc.SelectedIndex.Equals(0))
                {
                    getDate();
                    p = new SqlParameter[5];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
                    p[3].SqlDbType = SqlDbType.Int;
                    p[4] = new SqlParameter("@Index", 2);
                    p[4].SqlDbType = SqlDbType.Int;

                    dt = ex.GetData("sp_BCDT_NVKD_Print_HinhThuc", p);

                    int STT = 0;
                    //ghi sheet con------------------------------------------
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        STT++;
                        #region Start
                        if (i > 0)
                        {
                            hinhthuc = dt.Rows[i]["HinhThuc"].ToString();

                            if (!hinhthuc.Equals(dt.Rows[i - 1]["HinhThuc"].ToString()))
                            {
                                cabom = 0;
                                STT = 0;
                                #region Footer
                                //-----------Footer
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(2);
                                cell.SetCellValue("Tổng cộng");
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;

                                for (int k = 5; k <= 15; k++)
                                {
                                    if (k != 6 && k != 8 && k != 9)
                                    {
                                        cell = row.CreateCell(k);
                                        cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                                        cell.Row.Height = 500;
                                        cell.CellStyle = hs4;
                                    }
                                }
                                //fix lai border
                                for (int h = 0; h <= 20; h++)
                                {
                                    if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 18 || h == 19 || h == 17 || h == 6 || h == 8 || h == 9)
                                    {
                                        r1c1 = row.CreateCell(h);
                                        r1c1.CellStyle = hs4;
                                        r1c1.Row.Height = 400;
                                    }

                                }
                                for (int ik = 0; ik < dt.Columns.Count; ik++)
                                {
                                    sheet.AutoSizeColumn(ik);
                                }
                                //---End Footer
                                #endregion
                                sheet = workbook.CreateSheet(cl.EscapeSheetName(hinhthuc));
                                rowIndex = 0;

                                //Start header-----------------------------------------------------------------------
                                #region Header
                                //tao hnag dau tien

                                //cong ty
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(0, 0, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //chi nhanh
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(1, 1, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //tieu de bao cao
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
                                r1c1.CellStyle = hs2;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(2, 2, 0, 12);
                                sheet.AddMergedRegion(cra);

                                rowIndex++;

                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Nhân viên KD: " + hinhthuc);
                                r1c1.CellStyle = hs3;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(3, 3, 0, 12);
                                sheet.AddMergedRegion(cra);

                                //freeze panes
                                sheet.CreateFreezePane(0, 6);
                                #endregion
                                #region Header1-2

                                //header2
                                rowIndex++;
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("STT");
                                cell.CellStyle = hs4;
                                cell.Row.Height = 400;


                                //tao tieu de cot
                                for (int j = 0; j < header.Length; j++)
                                {
                                    cell = row.CreateCell(j + 1);
                                    cell.SetCellValue(header[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                }
                                #endregion
                                //End header-----------------------------------------------------------------------

                                STT++;

                            }
                        }
                        else
                        {
                            hinhthuc = "";
                            if (!hinhthuc.Equals(dt.Rows[i]["HinhThuc"].ToString()))
                            {
                                hinhthuc = dt.Rows[i]["HinhThuc"].ToString();
                                sheet = workbook.CreateSheet(cl.EscapeSheetName(hinhthuc));
                                rowIndex = 0;

                                //Start header-----------------------------------------------------------------------
                                #region Header
                                //tao hnag dau tien

                                //cong ty
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(0, 0, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //chi nhanh
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(1, 1, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //tieu de bao cao
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
                                r1c1.CellStyle = hs2;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(2, 2, 0, 12);
                                sheet.AddMergedRegion(cra);

                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Nhân viên KD: " + hinhthuc);
                                r1c1.CellStyle = hs3;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(3, 3, 0, 12);
                                sheet.AddMergedRegion(cra);

                                //freeze panes
                                sheet.CreateFreezePane(0, 6);
                                #endregion
                                #region Header1-2

                                //header2
                                rowIndex++;
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("STT");
                                cell.CellStyle = hs4;
                                cell.Row.Height = 400;


                                //tao tieu de cot
                                for (int j = 0; j < header.Length; j++)
                                {
                                    cell = row.CreateCell(j + 1);
                                    cell.SetCellValue(header[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                }
                                #endregion
                            }
                            for (int ik = 0; ik <= 19; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                        }
                        #endregion
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(0);
                        cell.SetCellValue((STT).ToString());
                        cell.CellStyle = hsCenter;


                        for (int j = 1; j < dt.Columns.Count; j++)
                        {
                            cell = row.CreateCell(j);
                            if ((j == 5 || j == 7) || j >= 9 && j != 16 && j != 20 && j != 19)
                            {
                                if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                                {
                                    cell.SetCellType(CellType.NUMERIC);
                                    cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                                }
                                cell.CellStyle = hsRight;
                            }
                            else
                            {
                                cell.SetCellValue(dt.Rows[i][j].ToString());

                                cell.SetCellValue(dt.Rows[i][j].ToString());
                                if (j <= 8 && j != 2)
                                    cell.CellStyle = hsCenter;
                                else
                                    if (j >= 9 && j != 16 && j != 20 && j != 19)
                                        cell.CellStyle = hsRight;
                                    else
                                        cell.CellStyle = headerLabelCellStyle;
                            }
                            cell.Row.Height = 500;
                        }
                        if (i > 0)
                        {
                            if (dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()) && dt.Rows[i][2].ToString().Equals(dt.Rows[i - 1][2].ToString()) && dt.Rows[i][16].ToString().Equals(dt.Rows[i - 1][16].ToString()) && dt.Rows[i][16].ToString().ToLower().Contains("ca"))
                            {
                                sodong++;
                                cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 17, 17);
                                sheet.AddMergedRegion(cra);
                            }
                            else
                                sodong = 0;
                        }
                        #region Footer
                        if (i == dt.Rows.Count - 1)
                        {
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(2);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 5; k <= 15; k++)
                            {
                                if (k != 6 && k != 8 && k != 9)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }
                            //fix lai border
                            for (int h = 0; h <= 20; h++)
                            {
                                if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 19 || h == 18 || h == 17 || h == 6 || h == 8 || h == 9)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }

                            }
                            for (int ik = 0; ik < dt.Columns.Count; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                        }
                        #endregion
                    }
                }
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string saveAsFileName = "";
                if (dlNhanVien.SelectedIndex > 0)
                    saveAsFileName = "BCDT-NVKD-" + dlNhanVien.SelectedItem.Text + "-" + ngaythang + ".xls";
                else saveAsFileName = "BCDT-NVKD-" + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
    }
    protected void dlNhanVien_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlNhanVien.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlHinhThuc.Items.Clear();
            dlHinhThuc.DataSource = ex.GetData("sp_BCDT_NVKD_LoadHinhThuc", p);
            dlHinhThuc.DataBind();
            dlHinhThuc.Items.Insert(0, new ListItem("--Chọn hình thức HĐ--", "0"));
            dlHinhThuc.SelectedIndex = 0;
        }
        else
        {
            dlHinhThuc.Items.Clear();
            dlHinhThuc.Items.Insert(0, new ListItem("--Chọn hình thức HĐ--", "0"));
            dlHinhThuc.SelectedIndex = 0;
        }
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }

    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;

    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;

    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        for (int rowIndex = GV.Rows.Count - 2; rowIndex >= 0; rowIndex--)
        {
            GridViewRow gvRow = GV.Rows[rowIndex];
            GridViewRow gvPreviousRow = GV.Rows[rowIndex + 1];

            if (gvRow.Cells[16].Text.Trim().ToLower().Contains("ca") && gvRow.Cells[1].Text.Equals(gvPreviousRow.Cells[1].Text) && gvRow.Cells[2].Text.Equals(gvPreviousRow.Cells[2].Text) && gvRow.Cells[16].Text.Equals(gvPreviousRow.Cells[16].Text) && gvRow.Cells[17].Text.Equals(gvPreviousRow.Cells[17].Text))
            {

                if (gvPreviousRow.Cells[1].RowSpan < 2)
                {
                    gvRow.Cells[1].RowSpan = 2;
                    gvRow.Cells[2].RowSpan = 2;
                    gvRow.Cells[16].RowSpan = 2;
                    gvRow.Cells[17].RowSpan = 2;
                }
                else
                {
                    gvRow.Cells[1].RowSpan = gvPreviousRow.Cells[1].RowSpan + 1;
                    gvRow.Cells[2].RowSpan = gvPreviousRow.Cells[2].RowSpan + 1;
                    gvRow.Cells[16].RowSpan = gvPreviousRow.Cells[16].RowSpan + 1;
                    gvRow.Cells[17].RowSpan = gvPreviousRow.Cells[17].RowSpan + 1;
                }
                gvPreviousRow.Cells[1].Visible = false;
                gvPreviousRow.Cells[2].Visible = false;
                gvPreviousRow.Cells[16].Visible = false;
                gvPreviousRow.Cells[17].Visible = false;
            }
        }
    }
}